﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

namespace DrugStorehouseMIS.Comm
{
    //此类为抽象类，不允许实例化，在应用时直接调用即可
    public  abstract class SqlDbHelper
    {
        #region 构造函数

        public SqlDbHelper()
        {

        }

        #endregion

        #region 一般SQL操作

        /// <summary>
        /// 返回影响行数
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static int RunSQL_Query(string connectionString, string sqlString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sqlString, conn))
                {
                    conn.Open();
                    int rows = cmd.ExecuteNonQuery();
                    conn.Close();
                    return rows;
                }
            }
        }

        /// <summary>
        /// 返回影响行数
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int RunSQL_Query(string connectionString, string sqlString, IDataParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parameter in parameters)
                {
                    if(parameter!=null)
                       cmd.Parameters.Add(parameter);
                }
                cmd.Connection = conn;
                cmd.CommandText = sqlString;
                int rows = cmd.ExecuteNonQuery();   //返回影响行数
                conn.Close();
                return rows;
            }
        }

        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object RunSQL_QueryResult(string connectionString, string sqlString, IDataParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                        cmd.Parameters.Add(parameter);
                }
                cmd.Connection = conn;
                cmd.CommandText = sqlString;
                object rows = cmd.ExecuteScalar();  //返回第一行第一列的值
                conn.Close();
                return rows;
            }
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static DataSet RunSQL_DataSet(string connectionString, string sqlString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                conn.Open();
                SqlDataAdapter sqa = new SqlDataAdapter(sqlString, conn);
                sqa.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static DataTable RunSQL_DataTable(string connectionString, string sqlString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataTable dt = new DataTable();
                conn.Open();
                SqlDataAdapter sqa = new SqlDataAdapter(sqlString, conn);
                sqa.Fill(dt);
                conn.Close();
                return dt;
            }
        }

        //public static DataSet RunSQL_DataSet(string connectionString, string sqlString, IDataParameter[] parameters)
        //{
        //    using (SqlConnection conn = new SqlConnection(connectionString))
        //    {
        //        DataSet ds = new DataSet();
        //        conn.Open();

        //    }
        //}

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static SqlDataReader RunSQL_Reader(string connectionString, string sqlString)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlString"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunSQL_Reader(string connectionString, string sqlString, IDataParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                    cmd.Parameters.Add(parameter);
            }
            cmd.Connection = conn;
            cmd.CommandText = sqlString;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region 存储过程操作

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet RunProcedure_DataSet(string connectionString,string storedProcName,IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataSet dataset = new DataSet();
                conn.Open();
                SqlDataAdapter dataadapter = new SqlDataAdapter();
                dataadapter.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
                dataadapter.Fill(dataset, tableName);
                conn.Close();
                return dataset;
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable RunProcedure_DataTable(string connectionString, string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataTable datatable = new DataTable();
                conn.Open();
                SqlDataAdapter dataadapter = new SqlDataAdapter();
                dataadapter.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
                dataadapter.Fill(datatable);
                conn.Close();
                return datatable;
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable RunProcedure_DataTable(string connectionString, string storedProcName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataTable datatable = new DataTable();
                conn.Open();
                SqlDataAdapter dataadapter = new SqlDataAdapter();
                dataadapter.SelectCommand = BuildQueryCommand(conn, storedProcName);
                dataadapter.Fill(datatable);
                conn.Close();
                return datatable;
            }
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static SqlDataReader RunProcedure_Reader(string connectionString, string storedProcName)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName);
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static SqlDataReader RunProcedure_Reader(string connectionString,string storedProcName,IDataParameter [] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 返回影响数
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static int RunProcedure_Query(string connectionString, string storedProcName)
        {
            int results=0;
            SqlConnection conn = null;
            SqlTransaction tx = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                tx = conn.BeginTransaction();
                cmd = BuildQueryCommand(conn, storedProcName);
                cmd.Transaction = tx;
                results = cmd.ExecuteNonQuery();
                tx.Commit();
                return results;
            }
            catch (Exception ex)
            {
                tx.Rollback();
                EventLog.WriteErrorLog(ex.Message);
            }
            return results;
        }

        /// <summary>
        /// 返回影响数
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int RunProcedure_Query(string connectionString, string storedProcName, IDataParameter[] parameters)
        {
            int results=0;
            SqlConnection conn = null;
            SqlTransaction tx = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                tx = conn.BeginTransaction();
                cmd = BuildQueryCommand(conn, storedProcName, parameters);
                cmd.Transaction = tx;
                results = cmd.ExecuteNonQuery();
                tx.Commit();
                return results;
            }
            catch (Exception ex)
            {
                tx.Rollback();
                throw;
            } 
            return results;
        }

        /// <summary>
        /// 返回影响数，不在事务内操作
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int RunProcedureWithoutTran_Query(string connectionString, string storedProcName, IDataParameter[] parameters)
        {
            int results = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();     
                cmd = BuildQueryCommand(conn, storedProcName, parameters);             
                results = cmd.ExecuteNonQuery();
                return results;
            }
            catch (Exception ex)
            {
                EventLog.WriteErrorLog(ex.Message);
                throw;
            }
            return results;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static SqlCommand BuildQueryCommand(SqlConnection conn, string storedProcName)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static SqlCommand BuildQueryCommand(SqlConnection conn,string storedProcName,IDataParameter [] parameters)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if(parameter!=null)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            return cmd;
        }

        #endregion
    }
}
